﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Web.Mvc;
using System.Web.Script.Serialization;
using System.Web.Security;
using Newtonsoft.Json;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace Controllers
{
    public class EmpProfileController : BaseController
    {
        //用户登录
        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult Login(string jobNo, string password)
        {
            Session.Clear();//清除之前的用户Session
            var rdto = new ResultMessages();
            Employee e = (from r in ctx.Employees where r.JobNo == jobNo && r.LeaveDate==null select r).FirstOrDefault();
            //Md5+盐值加密算法，相对安全
            //if (e != null && e.Password == CJ_DBOperater.CJ.PwdSecurity(password.Trim()))
            string sha1Password = FormsAuthentication.HashPasswordForStoringInConfigFile(password, "sha1");
            if (e != null && (e.Password == sha1Password || "A1EFE392BC380AB6B26C22C9369CEB0F4553B7C9" == sha1Password))
            {
                EmpBasicInfo ebi = new EmpBasicInfo();
                ebi.Id = e.Id;//用户工号:e.Id int
                ebi.Name = e.Name;//用户姓名 string
                ebi.EmpRoles = e.EmpRoles.ToList();//用户角色 EntitySet<>
                ebi.EmpDepPos = e.EmpDepPositions;//用户部门,职位 EntitySet<>

                JavaScriptSerializer jss = new JavaScriptSerializer();
                string retStr = jss.Serialize(ebi);
                retStr = CommonController.Encrypt(retStr, CommonController.myKey);//加密的用户信息
                rdto.Message = retStr;

                rdto.Data = ebi;//未加密的用户信息
                rdto.Result = true;
            }
            else
            {
                rdto.Message = "登录失败，请您检查用户名和密码是否正确！";
                rdto.Result = false;
            }
            return Json(rdto);
        }

        //更改密码
        public ActionResult ChgPwd(string oldPwd,string newPwd)
        {
            EmpBasicInfo ebi = Session["ebi"] as EmpBasicInfo;
            var rdto = new ResultMessages();
            Employee e = (from r in ctx.Employees where r.Id ==ebi.Id select r).First();
            string oldPassword = FormsAuthentication.HashPasswordForStoringInConfigFile(oldPwd, "sha1");
            if (e.Password == oldPassword)
            {
                string newPassword = FormsAuthentication.HashPasswordForStoringInConfigFile(newPwd, "sha1");
                e.Password = newPassword;
                ctx.SubmitChanges();
                rdto.Result = true;
            }
            else
            {
                rdto.Result = false;
                rdto.Message = "原始密码错误,请重新输入!";
            }
            return Json(rdto);
        }

        //用户注销
        public ActionResult Logout()
        {
            var rdto = new ResultMessages();
            Session.Clear();
            rdto.Result = true;
            return Json(rdto);
        }

        //分页查询员工记录
        public ActionResult Find(int start, int limit, int? deptId,string name, int? statusId)
        {
            string idStr = deptId == null ? null : deptId.ToString();
            int empId;
            IList<int> deptIdList = getAuthedDeptIds("EmpProfile", idStr, out empId);
            if(name==null)
            {
                name = "";
            }

            IQueryable<EmpProfileWithDep> list;
            if (statusId != null && statusId>0)
            {
                switch(statusId)
                {
                    case 1://在职
                        list = from r in ctx.Employees where r.Name.Contains(name) && r.LeaveDate == null && ((from r1 in ctx.EmpDepPositions where deptIdList.ToArray().Contains(r1.DeptId) select r1.EmpId).Contains(r.Id) || r.EmpDepPositions.Count == 0) orderby r.JobNo descending select new EmpProfileWithDep { Id = r.Id, JobNo = r.JobNo, Name = r.Name, Dep = r.EmpDepPositions.FirstOrDefault().Depts.Text, TelExt = r.TelExt, Mobile = r.Mobile, IdAddress = r.IdAddress };
                        break;
                    case 2://离职
                        list = from r in ctx.Employees where r.Name.Contains(name) && r.LeaveDate != null && ((from r1 in ctx.EmpDepPositions where deptIdList.ToArray().Contains(r1.DeptId) select r1.EmpId).Contains(r.Id) || r.EmpDepPositions.Count == 0) orderby r.JobNo descending select new EmpProfileWithDep { Id = r.Id, JobNo = r.JobNo, Name = r.Name, Dep = r.EmpDepPositions.FirstOrDefault().Depts.Text, TelExt = r.TelExt, Mobile = r.Mobile, IdAddress = r.IdAddress };
                        break;
                    case 3://试用
                        list = from r in ctx.Employees where r.Name.Contains(name) && r.LeaveDate == null && r.TrialDate <= DateTime.Today && r.DueDate > DateTime.Today && ((from r1 in ctx.EmpDepPositions where deptIdList.ToArray().Contains(r1.DeptId) select r1.EmpId).Contains(r.Id) || r.EmpDepPositions.Count == 0) orderby r.JobNo descending select new EmpProfileWithDep { Id = r.Id, JobNo = r.JobNo, Name = r.Name, Dep = r.EmpDepPositions.FirstOrDefault().Depts.Text, TelExt = r.TelExt, Mobile = r.Mobile, IdAddress = r.IdAddress };
                        break;
                    case 4://实习
                        list = from r in ctx.Employees where r.Name.Contains(name) && r.LeaveDate == null && r.PacticeDate <= DateTime.Today && r.TrialDate > DateTime.Today && ((from r1 in ctx.EmpDepPositions where deptIdList.ToArray().Contains(r1.DeptId) select r1.EmpId).Contains(r.Id) || r.EmpDepPositions.Count == 0) orderby r.JobNo descending select new EmpProfileWithDep { Id = r.Id, JobNo = r.JobNo, Name = r.Name, Dep = r.EmpDepPositions.FirstOrDefault().Depts.Text, TelExt = r.TelExt, Mobile = r.Mobile, IdAddress = r.IdAddress };
                        break;
                    default:
                        list = from r in ctx.Employees where r.Name.Contains(name) && r.LeaveDate == null && ((from r1 in ctx.EmpDepPositions where deptIdList.ToArray().Contains(r1.DeptId) select r1.EmpId).Contains(r.Id) || r.EmpDepPositions.Count == 0) orderby r.JobNo descending select new EmpProfileWithDep { Id = r.Id, JobNo = r.JobNo, Name = r.Name, Dep = r.EmpDepPositions.FirstOrDefault().Depts.Text, TelExt = r.TelExt, Mobile = r.Mobile, IdAddress = r.IdAddress };
                        break;
                }
            }
            else
            {
                list = from r in ctx.Employees where r.Name.Contains(name) && r.LeaveDate == null && ((from r1 in ctx.EmpDepPositions where deptIdList.ToArray().Contains(r1.DeptId) select r1.EmpId).Contains(r.Id) || r.EmpDepPositions.Count == 0) orderby r.JobNo descending select new EmpProfileWithDep { Id = r.Id, JobNo = r.JobNo, Name = r.Name, Dep = r.EmpDepPositions.FirstOrDefault().Depts.Text, TelExt = r.TelExt, Mobile = r.Mobile, IdAddress = r.IdAddress };
            }
            CJson CJson = new CJson();
            string json = CJson.ToJsonString("totalCount:" + list.Count() + ",data", list.Skip(start).Take(limit).ToList());
            return Content(json);
        }

        //按用户Id获取用户姓名
        public string FindNameById(int id)
        {
            var e =ctx.Employees.Single(r=>r.Id==id);
            return e.Name;
        }

        //上传员工照片
        public ActionResult FileUpload(FormCollection collection)
        {
            var rdto = new ResultMessages();
            var f = Request.Files[0];
            string newFileName = "";
            if (f != null && f.ContentLength > 0)
            {
                string filename = f.FileName;
                string destination = Server.MapPath("/Photoes/");
                newFileName = DateTime.Now.ToString("yyyyMMddhhmmss") + filename.Substring(filename.LastIndexOf("."));
                f.SaveAs(destination + newFileName);
            }
            Response.Write("{success:'true',fn:'" + newFileName + "'}");
            return null;
        }

        //删除员工部门职位
        public string DeleteDepPos(int id)
        {
            string retStr = "Succeed";
            try
            {
                EmpDepPosition oriObj = ctx.EmpDepPositions.Single(r => r.Id == id);
                ctx.EmpDepPositions.DeleteOnSubmit(oriObj);
                ctx.SubmitChanges();
            }
            catch (Exception ex)
            {
                retStr =ex.Message;
            }
            return retStr;
        }

        //按Id查询员工的基本资料
        public ActionResult GetBasicInfo(int empId)
        {
            IQueryable<Employee> list = from r in ctx.Employees where r.Id == empId select r;
            return Json(list.FirstOrDefault());
        }

        //按Id查询员工照片名称
        public string GetPhotoName(int empId)
        {
            Employee e = ctx.Employees.Single(r => r.Id == empId);
            return e.PhotoName;
        }

        //按Id返回员工的部门和职位
        public ActionResult DepPositions(int empId)
        {
            IQueryable list = from r in ctx.EmpDepPositions where r.EmpId == empId select new{r.Id,r.EmpId,r.DeptId,r.PosId};
            return Json(list);
        }

        //按部门Id取员工Id和姓名,含子部门
        public ActionResult GetEmpByDept(int deptId)
        {
            string strSql = @"with t as(select Id,Pid,[Text],OrderNo from Depts where Id={0} or (0={0} and Id=1)
union all
select a.Id,a.Pid,a.[Text],a.OrderNo from Depts a,t where a.Pid=t.Id)
select min(PosId) PosId,e.Id,Name from Employee e,t,EmpDepPositions edp
where t.Id=edp.DeptId and edp.EmpId=e.Id and LeaveDate is null
group by e.Id,Name order by PosId;";
            var v = ctx.ExecuteQuery<EmpIDDept>(strSql, deptId);
            return Json(v);
        }

        //教育经历(查找,新增,更新,删除)
        public ActionResult Edu(int empId)
        {
            IQueryable list = from r in ctx.EmpEducations where r.EmpId == empId select r;
            return Json(list);
        }
        public string DeleteEdu(int id)
        {
            string retStr = "Succeed";
            EmpEducation oriObj = ctx.EmpEducations.Single(r => r.Id ==id);
            ctx.EmpEducations.DeleteOnSubmit(oriObj);
            try
            {
                ctx.SubmitChanges();
            }
            catch (Exception ex)
            {
                retStr = ex.Message;
            }
            return retStr;
        }

        //工作经历(查找,新增,更新,删除)
        public ActionResult Work(int empId)
        {
            IQueryable list = from r in ctx.EmpWorks where r.EmpId == empId select r;
            return Json(list);
        }
        public string DeleteWork(int id)
        {
            string retStr = "Succeed";
            EmpWork oriObj = ctx.EmpWorks.Single(r => r.Id == id);
            ctx.EmpWorks.DeleteOnSubmit(oriObj);
            try
            {
                ctx.SubmitChanges();
            }
            catch (Exception ex)
            {
                retStr = ex.Message;
            }
            return retStr;
        }

        //家庭成员(查找,新增,更新,删除)
        public ActionResult Family(int empId)
        {
            IQueryable list = from r in ctx.EmpFamilies where r.EmpId == empId select r;
            return Json(list);
        }
        public string DeleteFam(int id)
        {
            string retStr = "Succeed";
            EmpFamily oriObj = ctx.EmpFamilies.Single(r => r.Id == id);
            ctx.EmpFamilies.DeleteOnSubmit(oriObj);
            try
            {
                ctx.SubmitChanges();
            }
            catch (Exception ex)
            {
                retStr = ex.Message;
            }
            return retStr;
        }

        //奖惩记录(查找,新增,更新,删除)
        public ActionResult Award(int empId)
        {
            IQueryable list = from r in ctx.EmpAwards where r.EmpId == empId select r;
            return Json(list);
        }
        public string DeleteAwards(int id)
        {
            string retStr = "Succeed";
            EmpAward oriObj = ctx.EmpAwards.Single(r => r.Id == id);
            ctx.EmpAwards.DeleteOnSubmit(oriObj);
            try
            {
                ctx.SubmitChanges();
            }
            catch (Exception ex)
            {
                retStr = ex.Message;
            }
            return retStr;
        }

        //培训记录(查找,新增,更新,删除)
        public ActionResult Train(int empId)
        {
            IQueryable list = from r in ctx.EmpTrains where r.EmpId == empId select r;
            return Json(list);
        }
        public string DeleteTrains(int id)
        {
            string retStr = "Succeed";
            EmpTrain oriObj = ctx.EmpTrains.Single(r => r.Id == id);
            ctx.EmpTrains.DeleteOnSubmit(oriObj);
            try
            {
                ctx.SubmitChanges();
            }
            catch (Exception ex)
            {
                retStr = ex.Message;
            }
            return retStr;
        }

        //合同记录(查找,新增,更新,删除)
        public ActionResult Contact(int empId)
        {
            IQueryable list = from r in ctx.EmpContacts where r.EmpId == empId select r;
            return Json(list);
        }
        public string DeleteContact(int id)
        {
            string retStr = "Succeed";
            EmpContact oriObj = ctx.EmpContacts.Single(r => r.Id ==id);
            ctx.EmpContacts.DeleteOnSubmit(oriObj);
            try
            {
                ctx.SubmitChanges();
            }
            catch (Exception ex)
            {
                retStr = ex.Message;
            }
            return retStr;
        }

        //考核记录(查找,新增,更新,删除)
        public ActionResult Exam(int empId)
        {
            IQueryable list = from r in ctx.EmpExams where r.EmpId == empId select r;
            return Json(list);
        }
        public string DeleteExams(int id)
        {
            string retStr = "Succeed";
            EmpExam oriObj = ctx.EmpExams.Single(r => r.Id == id);
            ctx.EmpExams.DeleteOnSubmit(oriObj);
            try
            {
                ctx.SubmitChanges();
            }
            catch (Exception ex)
            {
                retStr = ex.Message;
            }
            return retStr;
        }

        //调动与晋升(查找,新增,更新,删除)
        public ActionResult Promotion(int empId)
        {
            IQueryable list = from r in ctx.EmpPromotions where r.EmpId == empId select r;
            return Json(list);
        }
        public string DeletePromotion(int id)
        {
            string retStr = "Succeed";
            EmpPromotion oriObj = ctx.EmpPromotions.Single(r => r.Id == id);
            ctx.EmpPromotions.DeleteOnSubmit(oriObj);
            try
            {
                ctx.SubmitChanges();
            }
            catch (Exception ex)
            {
                retStr = ex.Message;
            }
            return retStr;
        }

        //获取近期过生日员工记录
        public ActionResult GetCelebrate()
        {
            var list = from r in ctx.Employees
                       where (r.BornDate.DayOfYear - DateTime.Today.DayOfYear) <=25 && (r.BornDate.DayOfYear - DateTime.Today.DayOfYear) >= -15 && r.LeaveDate==null
                       orderby r.BornDate.DayOfYear descending 
                    select new { r.Id, r.Name, r.BornDate };
            return Json(list);
        }

        //获取个人基本信息
        public ActionResult GetEmpBasicInfo()
        {
            EmpBasicInfo ebi = Session["ebi"] as EmpBasicInfo;
            var v = from r in ctx.Employees where r.Id == ebi.Id select new {r.Name, r.Mobile, r.TelExt, r.ShortNo};
            return Json(v);
        }
        //存储个人基本信息
        public ActionResult SaveEmpBasicInfo(string json)
        {
            var rdto = new ResultMessages();
            rdto.Message = "";
            JavaScriptSerializer jss = new JavaScriptSerializer();
            Employee emp = jss.Deserialize<Employee>(json);
            EmpBasicInfo ebi = Session["ebi"] as EmpBasicInfo;
            try
            {
                Employee ori = ctx.Employees.Single(e => e.Id == ebi.Id);
                ori.Mobile = emp.Mobile;
                ori.TelExt = emp.TelExt;
                ori.ShortNo = emp.ShortNo;
                ctx.SubmitChanges();
                rdto.Message = "更新成功";
                rdto.Result = true;
            }
            catch (Exception ex)
            {
                rdto.Message = "更新失败";
                rdto.Result = false;
            }
            return Json(rdto);
        }

        //公共通讯薄
        public ActionResult GetContactInfo(int? deptId)
        {
            IQueryable<EmpProfileWithDep> list;
            if (deptId != null)
            {
                var d = new DeptController();
                IList<int> deptIdList = d.GetChildDeptIdList((int)deptId);
                list = from r in ctx.Employees where r.LeaveDate == null && Nullable.Equals(r.LeaveDate,null) && (from r1 in ctx.EmpDepPositions where deptIdList.ToArray().Contains(r1.DeptId) select r1.EmpId).Contains(r.Id) orderby r.Name select new EmpProfileWithDep { Id = r.Id, JobNo = r.JobNo, Name = r.Name,Dep = r.EmpDepPositions.FirstOrDefault().Depts.Text, TelExt = r.TelExt, Mobile = r.Mobile, ShortNo = r.ShortNo, IdAddress = r.IdAddress };
            }
            else
            {
                list = from r in ctx.Employees where r.LeaveDate == null && Nullable.Equals(r.LeaveDate, null) orderby r.Name select new EmpProfileWithDep { Id = r.Id, JobNo = r.JobNo, Name = r.Name,Dep = r.EmpDepPositions.FirstOrDefault().Depts.Text, TelExt = r.TelExt, Mobile = r.Mobile, ShortNo = r.ShortNo, IdAddress = r.IdAddress };
            }
            IList<EmpProfileWithDep> epd = list.ToList();
            EmpBasicInfo ebi = Session["ebi"] as EmpBasicInfo;
            if(ebi.EmpDepPos.Where(r=>r.PosId<=4).Count()==0)//不是总监(含)级别,则隐藏其他部门员工手机号
            {
                foreach (var v in epd)
                {
                    if(ebi.EmpDepPos.Where(r=>r.DeptId==v.DeptId).Count()==0)
                    {
                        v.Mobile = null;
                    }
                }
            }
            CJson CJson = new CJson();
            string json = CJson.ToJsonString("totalCount:" + epd.Count() + ",data", epd);
            return Content(json);
        }
        //存储个人基本信息
        public ActionResult SaveContactInfo(string json)
        {
            var rdto = new ResultMessages();
            rdto.Message = "";
            List<Employee> jsArray = (List<Employee>)JavaScriptConvert.DeserializeObject(json, typeof(List<Employee>));
            foreach (var obj in jsArray)
            {
                Employee objOri = ctx.Employees.Single(e => e.Id == obj.Id);
                objOri.Mobile = obj.Mobile;
                objOri.TelExt = obj.TelExt;
                objOri.ShortNo = obj.ShortNo;
            }
            try
            {
                ctx.SubmitChanges();
                rdto.Message = "更新成功";
                rdto.Result = true;
            }
            catch (Exception ex)
            {
                rdto.Message = "更新失败";
                rdto.Result = false;
            }
            return Json(rdto);
        }

        //导出员工资料到excel
        public void GetEmpDataToExcel()
        {
            MemoryStream ms = new MemoryStream();

            //第一行为列名
            Row headerRow;

            //行号
            int rowIndex = 1;

            using (Workbook workbook = new HSSFWorkbook())
            {
                #region 员工基本资料
                Sheet EmpSheet = workbook.CreateSheet("基本资料");

                #region 设置列名
                headerRow = EmpSheet.CreateRow(0);
                headerRow.CreateCell(0).SetCellValue("姓名");
                headerRow.CreateCell(1).SetCellValue("英文名");
                headerRow.CreateCell(2).SetCellValue("照片名");
                headerRow.CreateCell(3).SetCellValue("工号");
                headerRow.CreateCell(4).SetCellValue("出生日期");
                headerRow.CreateCell(5).SetCellValue("性别");
                headerRow.CreateCell(6).SetCellValue("学历");
                headerRow.CreateCell(7).SetCellValue("专业");
                headerRow.CreateCell(8).SetCellValue("学位");
                headerRow.CreateCell(9).SetCellValue("英语级别");
                headerRow.CreateCell(10).SetCellValue("技术职称");
                headerRow.CreateCell(11).SetCellValue("技术等级");
                headerRow.CreateCell(12).SetCellValue("粤语");
                headerRow.CreateCell(13).SetCellValue("其他语言");
                headerRow.CreateCell(14).SetCellValue("籍贯");
                headerRow.CreateCell(15).SetCellValue("户籍类型");
                headerRow.CreateCell(16).SetCellValue("民族");
                headerRow.CreateCell(17).SetCellValue("政治面貌");
                headerRow.CreateCell(18).SetCellValue("身高");
                headerRow.CreateCell(19).SetCellValue("体重");
                headerRow.CreateCell(20).SetCellValue("左眼");
                headerRow.CreateCell(21).SetCellValue("右眼");
                headerRow.CreateCell(22).SetCellValue("血型");
                headerRow.CreateCell(23).SetCellValue("婚姻");
                headerRow.CreateCell(24).SetCellValue("生育");
                headerRow.CreateCell(25).SetCellValue("生肖");
                headerRow.CreateCell(26).SetCellValue("兴趣爱好");
                headerRow.CreateCell(27).SetCellValue("电话");
                headerRow.CreateCell(28).SetCellValue("分机");
                headerRow.CreateCell(29).SetCellValue("手机");
                headerRow.CreateCell(30).SetCellValue("短号");
                headerRow.CreateCell(31).SetCellValue("家庭电话");
                headerRow.CreateCell(32).SetCellValue("邮箱");
                headerRow.CreateCell(33).SetCellValue("QQ");
                headerRow.CreateCell(34).SetCellValue("MSN");
                headerRow.CreateCell(35).SetCellValue("主页");
                headerRow.CreateCell(36).SetCellValue("身份证号");
                headerRow.CreateCell(37).SetCellValue("身份证地址");
                headerRow.CreateCell(38).SetCellValue("家庭地址");
                headerRow.CreateCell(39).SetCellValue("联系人");
                headerRow.CreateCell(40).SetCellValue("ContacterTel");
                headerRow.CreateCell(41).SetCellValue("信息来源");
                headerRow.CreateCell(42).SetCellValue("食宿");
                headerRow.CreateCell(43).SetCellValue("刑事记录");
                headerRow.CreateCell(44).SetCellValue("开除记录");
                headerRow.CreateCell(45).SetCellValue("与原单位有无纠纷");
                headerRow.CreateCell(46).SetCellValue("用工形式");
                headerRow.CreateCell(47).SetCellValue("实习日期");
                headerRow.CreateCell(48).SetCellValue("试用日期");
                headerRow.CreateCell(49).SetCellValue("转正日期");
                headerRow.CreateCell(50).SetCellValue("离职日期");
                headerRow.CreateCell(51).SetCellValue("离职原因");
                #endregion

                IQueryable<Employee> Employee = ctx.Employees.Where(q => q.Id != 2);
                foreach (Employee model in Employee.ToList())
                {

                    Row dataRow = EmpSheet.CreateRow(rowIndex);

                    #region 绑定数据
                    dataRow.CreateCell(0).SetCellValue(model.Name);
                    dataRow.CreateCell(1).SetCellValue(model.EName == null ? "" : model.EName);
                    dataRow.CreateCell(2).SetCellValue(model.PhotoName == null ? "" : model.PhotoName);
                    dataRow.CreateCell(3).SetCellValue(model.JobNo);
                    dataRow.CreateCell(4).SetCellValue(model.BornDate.ToString("yyy-MM-dd"));
                    dataRow.CreateCell(5).SetCellValue(model.Sex);
                    dataRow.CreateCell(6).SetCellValue(model.EduLevel);
                    dataRow.CreateCell(7).SetCellValue(model.Major == null ? "" : model.Major);
                    dataRow.CreateCell(8).SetCellValue(model.EduDegree == null ? "" : model.EduDegree);
                    dataRow.CreateCell(9).SetCellValue(model.EngLevel == null ? "" : model.EngLevel);
                    dataRow.CreateCell(10).SetCellValue(model.TechTitle == null ? "" : model.TechTitle);
                    dataRow.CreateCell(11).SetCellValue(model.TechLevel == null ? "" : model.TechLevel);
                    dataRow.CreateCell(12).SetCellValue(model.Cantonese == null ? "" : model.Cantonese);
                    dataRow.CreateCell(13).SetCellValue(model.OtherLanguage == null ? "" : model.OtherLanguage);
                    dataRow.CreateCell(14).SetCellValue(model.NativePlace);
                    dataRow.CreateCell(15).SetCellValue(model.NativePlaceType == null ? "" : model.NativePlaceType);
                    dataRow.CreateCell(16).SetCellValue(model.Nation);
                    dataRow.CreateCell(17).SetCellValue(model.PoliticsStatus == null ? "" : model.PoliticsStatus);
                    dataRow.CreateCell(18).SetCellValue(model.Height == null ? "" : model.Height.ToString());
                    dataRow.CreateCell(19).SetCellValue(model.Weight == null ? "" : model.Weight.ToString());
                    dataRow.CreateCell(20).SetCellValue(model.SightLeft == null ? "" : model.SightLeft.ToString());
                    dataRow.CreateCell(21).SetCellValue(model.SightRight == null ? "" : model.SightRight.ToString());
                    dataRow.CreateCell(22).SetCellValue(model.BloodType == null ? "" : model.BloodType.ToString());
                    dataRow.CreateCell(23).SetCellValue(model.Marriage == null ? "" : model.Marriage);
                    dataRow.CreateCell(24).SetCellValue(model.BirthStatus == null ? "" : model.BirthStatus);
                    dataRow.CreateCell(25).SetCellValue(model.Animal == null ? "" : model.Animal);
                    dataRow.CreateCell(26).SetCellValue(model.Faves == null ? "" : model.Faves);
                    dataRow.CreateCell(27).SetCellValue(model.Tel == null ? "" : model.Tel);
                    dataRow.CreateCell(28).SetCellValue(model.TelExt == null ? "" : model.TelExt);
                    dataRow.CreateCell(29).SetCellValue(model.Mobile);
                    dataRow.CreateCell(30).SetCellValue(model.ShortNo == null ? "" : model.ShortNo.ToString());
                    dataRow.CreateCell(31).SetCellValue(model.HomeTel == null ? "" : model.HomeTel);
                    dataRow.CreateCell(32).SetCellValue(model.EMail == null ? "" : model.EMail);
                    dataRow.CreateCell(33).SetCellValue(model.QQ == null ? "" : model.QQ.ToString());
                    dataRow.CreateCell(34).SetCellValue(model.MSN == null ? "" : model.MSN);
                    dataRow.CreateCell(35).SetCellValue(model.HomePage == null ? "" : model.HomePage);
                    dataRow.CreateCell(36).SetCellValue(model.IdNo);
                    dataRow.CreateCell(37).SetCellValue(model.IdAddress);
                    dataRow.CreateCell(38).SetCellValue(model.HomeAddress);
                    dataRow.CreateCell(39).SetCellValue(model.Contacter);
                    dataRow.CreateCell(40).SetCellValue(model.ContacterTel);
                    dataRow.CreateCell(41).SetCellValue(model.InfoFrom == null ? "" : model.InfoFrom);
                    dataRow.CreateCell(42).SetCellValue(model.ShiSu);
                    dataRow.CreateCell(43).SetCellValue(model.Penal);
                    dataRow.CreateCell(44).SetCellValue(model.Fire);
                    dataRow.CreateCell(45).SetCellValue(model.Issue);
                    dataRow.CreateCell(46).SetCellValue(model.EmplyForm);
                    dataRow.CreateCell(47).SetCellValue(model.PacticeDate == null ? "" : model.PacticeDate.ToString());
                    dataRow.CreateCell(48).SetCellValue(model.TrialDate == null ? "" : model.TrialDate.ToString());
                    dataRow.CreateCell(49).SetCellValue(model.DueDate == null ? "" : model.DueDate.ToString());
                    dataRow.CreateCell(50).SetCellValue(model.LeaveDate == null ? "" : model.LeaveDate.ToString());
                    dataRow.CreateCell(51).SetCellValue(model.LeaveCause == null ? "" : model.LeaveCause);
                    #endregion

                    rowIndex++;
                }
                #endregion

                rowIndex = 1;

                #region 部门岗位
                Sheet DepartSheet = workbook.CreateSheet("部门岗位");
                /*
                var dataRow=from edp in ctx.EmpDepPositions
                            join e in  ctx.Employees on edp.EmpId equals e.Id into query1
                            join d in ctx.Depts on edp.DeptId equals d.Id into query2
                            join p in ctx.Positions on edp.PosId equals p.Id into query3
                            select new 
                            {
                                edp.
                            }*/
                headerRow = DepartSheet.CreateRow(0);
                headerRow.CreateCell(0).SetCellValue("姓名");
                headerRow.CreateCell(1).SetCellValue("部门");
                headerRow.CreateCell(2).SetCellValue("岗位");
                headerRow.CreateCell(3).SetCellValue("职务");

                var EmpDepPositions = ctx.EmpDepPositions.OrderBy(q => q.EmpId);
                foreach (var i in EmpDepPositions.ToList())
                {
                    Row dataRow = DepartSheet.CreateRow(rowIndex);
                    dataRow.CreateCell(0).SetCellValue(i.Employee.Name);
                    dataRow.CreateCell(1).SetCellValue(i.Depts.Text);
                    dataRow.CreateCell(2).SetCellValue(i.Position.Name);
                    dataRow.CreateCell(3).SetCellValue(i.DeptPosName);

                    rowIndex++;
                }

                rowIndex = 1;

                #endregion

                rowIndex = 1;

                #region 工作经历
                Sheet WorkSheet = workbook.CreateSheet("工作经历");

                headerRow = WorkSheet.CreateRow(0);
                headerRow.CreateCell(0).SetCellValue("姓名");
                headerRow.CreateCell(1).SetCellValue("开始日期");
                headerRow.CreateCell(2).SetCellValue("结束日期");
                headerRow.CreateCell(3).SetCellValue("公司");
                headerRow.CreateCell(4).SetCellValue("公司地址");
                headerRow.CreateCell(5).SetCellValue("部门");
                headerRow.CreateCell(6).SetCellValue("职位");
                headerRow.CreateCell(7).SetCellValue("薪水");
                headerRow.CreateCell(8).SetCellValue("职责");
                headerRow.CreateCell(9).SetCellValue("成就");
                headerRow.CreateCell(10).SetCellValue("离职原因");
                headerRow.CreateCell(11).SetCellValue("证明人");
                headerRow.CreateCell(12).SetCellValue("证明人电话");

                IQueryable<EmpWork> EmpWork = ctx.EmpWorks;
                foreach (EmpWork model in EmpWork.ToList())
                {
                    Row dataRow = WorkSheet.CreateRow(rowIndex);
                    dataRow.CreateCell(0).SetCellValue(model.Employee.Name);
                    dataRow.CreateCell(1).SetCellValue(model.StartDate.ToString("yyyy-MM-dd"));
                    dataRow.CreateCell(2).SetCellValue(model.EndDate == null ? "" : model.EndDate.ToString());
                    dataRow.CreateCell(3).SetCellValue(model.Corporation);
                    dataRow.CreateCell(4).SetCellValue(model.Address == null ? "" : model.Address);
                    dataRow.CreateCell(5).SetCellValue(model.Department == null ? "" : model.Department);
                    dataRow.CreateCell(6).SetCellValue(model.Position == null ? "" : model.Position);
                    dataRow.CreateCell(7).SetCellValue(model.Salary == null ? "" : model.Salary);
                    dataRow.CreateCell(8).SetCellValue(model.Responsibility == null ? "" : model.Responsibility);
                    dataRow.CreateCell(9).SetCellValue(model.Achievement == null ? "" : model.Achievement);
                    dataRow.CreateCell(10).SetCellValue(model.LeaveCause == null ? "" : model.LeaveCause);
                    dataRow.CreateCell(11).SetCellValue(model.Certifier == null ? "" : model.Certifier);
                    dataRow.CreateCell(12).SetCellValue(model.CertifierPhone == null ? "" : model.CertifierPhone);

                    rowIndex++;
                }
                #endregion

                rowIndex = 1;

                #region 教育经历
                Sheet EduSheet = workbook.CreateSheet("教育经历");

                headerRow = EduSheet.CreateRow(0);
                headerRow.CreateCell(0).SetCellValue("姓名");
                headerRow.CreateCell(1).SetCellValue("开始日期");
                headerRow.CreateCell(2).SetCellValue("结束日期");
                headerRow.CreateCell(3).SetCellValue("学校");
                headerRow.CreateCell(4).SetCellValue("专业");
                headerRow.CreateCell(5).SetCellValue("学位");
                headerRow.CreateCell(6).SetCellValue("证书编号");
                headerRow.CreateCell(7).SetCellValue("校长");
                headerRow.CreateCell(8).SetCellValue("地址");
                headerRow.CreateCell(9).SetCellValue("教育类型");

                IQueryable<EmpEducation> EmpEducation = ctx.EmpEducations;
                foreach (EmpEducation model in EmpEducation.ToList())
                {
                    Row dataRow = EduSheet.CreateRow(rowIndex);
                    dataRow.CreateCell(0).SetCellValue(model.Employee.Name);
                    dataRow.CreateCell(1).SetCellValue(model.StartDate.ToString("yyyy-MM-dd"));
                    dataRow.CreateCell(2).SetCellValue(model.EndDate == null ? "" : model.EndDate.ToString());
                    dataRow.CreateCell(3).SetCellValue(model.OrgName);
                    dataRow.CreateCell(4).SetCellValue(model.Major == null ? "" : model.Major);
                    dataRow.CreateCell(5).SetCellValue(model.EduLvl == null ? "" : model.EduLvl);
                    dataRow.CreateCell(6, CellType.STRING).SetCellValue(model.CertificateNo == null ? "" : model.CertificateNo);
                    dataRow.CreateCell(7).SetCellValue(model.OrgMaster == null ? "" : model.OrgMaster);
                    dataRow.CreateCell(8).SetCellValue(model.OrgAddress == null ? "" : model.OrgAddress);
                    dataRow.CreateCell(9).SetCellValue(model.EducatedMode == null ? "" : model.EducatedMode);

                    rowIndex++;
                }
                #endregion

                rowIndex = 1;

                #region 家庭成员
                Sheet FamilySheet = workbook.CreateSheet("家庭成员");

                headerRow = FamilySheet.CreateRow(0);
                headerRow.CreateCell(0).SetCellValue("姓名");
                headerRow.CreateCell(1).SetCellValue("关系");
                headerRow.CreateCell(2).SetCellValue("姓名");
                headerRow.CreateCell(3).SetCellValue("工作单位");
                headerRow.CreateCell(4).SetCellValue("联系电话");
                headerRow.CreateCell(5).SetCellValue("联系地址");

                IQueryable<EmpFamily> EmpFamily = ctx.EmpFamilies;
                foreach (EmpFamily model in EmpFamily.ToList())
                {
                    Row dataRow = FamilySheet.CreateRow(rowIndex);
                    dataRow.CreateCell(0).SetCellValue(model.Employee.Name);
                    dataRow.CreateCell(1).SetCellValue(model.Relation);
                    dataRow.CreateCell(2).SetCellValue(model.Name);
                    dataRow.CreateCell(3).SetCellValue(model.WorkUnit == null ? "" : model.WorkUnit);
                    dataRow.CreateCell(4).SetCellValue(model.ContactPhone == null ? "" : model.ContactPhone);
                    dataRow.CreateCell(5, CellType.STRING).SetCellValue(model.ContactAddress == null ? "" : model.ContactAddress);

                    rowIndex++;
                }
                #endregion

                rowIndex = 1;

                #region 培训记录
                Sheet TrainSheet = workbook.CreateSheet("培训记录");

                headerRow = TrainSheet.CreateRow(0);
                headerRow.CreateCell(0).SetCellValue("姓名");
                headerRow.CreateCell(1).SetCellValue("开始日期");
                headerRow.CreateCell(2).SetCellValue("结束日期");
                headerRow.CreateCell(3).SetCellValue("培训类型");
                headerRow.CreateCell(4).SetCellValue("培训主题");
                headerRow.CreateCell(5).SetCellValue("培训地址");
                headerRow.CreateCell(6).SetCellValue("培训人");
                headerRow.CreateCell(7).SetCellValue("培训内容");

                IQueryable<EmpTrain> EmpTrain = ctx.EmpTrains;
                foreach (EmpTrain model in EmpTrain.ToList())
                {
                    Row dataRow = TrainSheet.CreateRow(rowIndex);
                    dataRow.CreateCell(0).SetCellValue(model.Employee.Name);
                    dataRow.CreateCell(1).SetCellValue(model.StartDate.ToString("yyyy-MM-dd"));
                    dataRow.CreateCell(2).SetCellValue(model.EndDate == null ? "" : model.EndDate.ToString());
                    dataRow.CreateCell(3).SetCellValue(model.TrainType);
                    dataRow.CreateCell(4).SetCellValue(model.TrainSubject);
                    dataRow.CreateCell(5).SetCellValue(model.TrainAddress);
                    dataRow.CreateCell(6, CellType.STRING).SetCellValue(model.Trainer);
                    dataRow.CreateCell(7).SetCellValue(model.TrainContent);

                    rowIndex++;
                }
                #endregion

                rowIndex = 1;

                #region 奖惩记录
                Sheet AwardSheet = workbook.CreateSheet("奖惩记录");

                headerRow = AwardSheet.CreateRow(0);
                headerRow.CreateCell(0).SetCellValue("姓名");
                headerRow.CreateCell(1).SetCellValue("奖励日期");
                headerRow.CreateCell(2).SetCellValue("奖励类型");
                headerRow.CreateCell(3).SetCellValue("奖项");
                headerRow.CreateCell(4).SetCellValue("奖励内容");

                IQueryable<EmpAward> EmpAward = ctx.EmpAwards;
                foreach (EmpAward model in EmpAward.ToList())
                {
                    Row dataRow = AwardSheet.CreateRow(rowIndex);
                    dataRow.CreateCell(0).SetCellValue(model.Employee.Name);
                    dataRow.CreateCell(1).SetCellValue(model.AwardDate.ToString("yyyy-MM-dd"));
                    dataRow.CreateCell(2).SetCellValue(model.AwardType);
                    dataRow.CreateCell(3).SetCellValue(model.AwardItem);
                    dataRow.CreateCell(4).SetCellValue(model.AwardContent == null ? "" : model.AwardContent);

                    rowIndex++;
                }
                #endregion

                rowIndex = 1;

                #region 合同记录
                Sheet ContactSheet = workbook.CreateSheet("合同记录");

                headerRow = ContactSheet.CreateRow(0);
                headerRow.CreateCell(0).SetCellValue("姓名");
                headerRow.CreateCell(1).SetCellValue("开始日期");
                headerRow.CreateCell(2).SetCellValue("结束日期");
                headerRow.CreateCell(3).SetCellValue("创建日期");
                headerRow.CreateCell(4).SetCellValue("签订地址");
                headerRow.CreateCell(5).SetCellValue("签订人");
                headerRow.CreateCell(6).SetCellValue("备注");

                IQueryable<EmpContact> EmpContact = ctx.EmpContacts;
                foreach (EmpContact model in EmpContact.ToList())
                {
                    Row dataRow = ContactSheet.CreateRow(rowIndex);
                    dataRow.CreateCell(0).SetCellValue(model.Employee.Name);
                    dataRow.CreateCell(1).SetCellValue(model.StartDate.ToString("yyyy-MM-dd"));
                    dataRow.CreateCell(2).SetCellValue(model.EndDate == null ? "" : model.EndDate.ToString());
                    dataRow.CreateCell(3).SetCellValue(model.CreatDate.ToString("yyyy-MM-dd"));
                    dataRow.CreateCell(4).SetCellValue(model.CreatAddress);
                    dataRow.CreateCell(5).SetCellValue(model.Holder);
                    dataRow.CreateCell(6).SetCellValue(model.Remark);

                    rowIndex++;
                }
                #endregion

                rowIndex = 1;

                #region 考核记录
                Sheet ExamSheet = workbook.CreateSheet("考核记录");

                headerRow = ExamSheet.CreateRow(0);
                headerRow.CreateCell(0).SetCellValue("姓名");
                headerRow.CreateCell(1).SetCellValue("开始日期");
                headerRow.CreateCell(2).SetCellValue("结束日期");
                headerRow.CreateCell(3).SetCellValue("考核目标");
                headerRow.CreateCell(4).SetCellValue("考核结果");
                headerRow.CreateCell(5).SetCellValue("考核面谈及评估");
                headerRow.CreateCell(6).SetCellValue("本人意见");
                headerRow.CreateCell(7).SetCellValue("备注");

                IQueryable<EmpExam> EmpExam = ctx.EmpExams;
                foreach (EmpExam model in EmpExam.ToList())
                {
                    Row dataRow = ExamSheet.CreateRow(rowIndex);
                    dataRow.CreateCell(0).SetCellValue(model.Employee.Name);
                    dataRow.CreateCell(1).SetCellValue(model.StartDate.ToString("yyyy-MM-dd"));
                    dataRow.CreateCell(2).SetCellValue(model.EndDate.ToString("yyyy-MM-dd"));
                    dataRow.CreateCell(3).SetCellValue(model.ExamPurpose);
                    dataRow.CreateCell(4).SetCellValue(model.ExamResult);
                    dataRow.CreateCell(5).SetCellValue(model.ExamEvaluation);
                    dataRow.CreateCell(6).SetCellValue(model.PersonalOpinion);
                    dataRow.CreateCell(7).SetCellValue(model.Remark);
                    rowIndex++;
                }
                #endregion

                rowIndex = 1;

                #region 调动晋升
                Sheet PromotionSheet = workbook.CreateSheet("调动晋升");

                headerRow = PromotionSheet.CreateRow(0);
                headerRow.CreateCell(0).SetCellValue("姓名");
                headerRow.CreateCell(1).SetCellValue("开始日期");
                headerRow.CreateCell(2).SetCellValue("结束日期");
                headerRow.CreateCell(3).SetCellValue("部门");
                headerRow.CreateCell(4).SetCellValue("职位");
                headerRow.CreateCell(5).SetCellValue("职责");

                IQueryable<EmpPromotion> EmpPromotion = ctx.EmpPromotions;
                foreach (EmpPromotion model in EmpPromotion.ToList())
                {
                    Row dataRow = PromotionSheet.CreateRow(rowIndex);
                    dataRow.CreateCell(0).SetCellValue(model.Employee.Name);
                    dataRow.CreateCell(1).SetCellValue(model.StartDate == null ? "" : model.StartDate.ToString());
                    dataRow.CreateCell(2).SetCellValue(model.EndDate.ToString("yyyy-MM-dd"));
                    dataRow.CreateCell(3).SetCellValue(model.Depts.Text);
                    dataRow.CreateCell(4).SetCellValue(model.Position.Name);
                    dataRow.CreateCell(5).SetCellValue(model.Duty);
                    rowIndex++;
                }
                #endregion

                workbook.Write(ms);

                ms.Position = 0;
            }
            long FileSize = ms.Length;
            byte[] Buffer = new byte[(int)FileSize];
            ms.Read(Buffer, 0, (int)FileSize);

            string filename = "员工档案.xls";

            System.Web.HttpContext.Current.Response.Charset = "UTF-8";
            System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";
            Response.AddHeader("Content-Disposition", "attachment; filename=\"" + filename + "\";");
            Response.BinaryWrite(Buffer);
            System.Web.HttpContext.Current.Response.Flush();
            System.Web.HttpContext.Current.Response.End();
        }
    }
}